package cn.mysdp.biz.facade;

import cn.mysdp.biz.domain.*;
import cn.mysdp.biz.dto.request.*;
import cn.mysdp.biz.dto.response.SdpSqlQueryResponse;
import cn.mysdp.biz.dto.response.SdpWorkspaceQueryResponse;
import cn.mysdp.biz.repository.SdpSqlMapper;
import cn.mysdp.biz.repository.SdpWorkspaceConfigMapper;
import cn.mysdp.utils.ConnectUtil;
import cn.mysdp.utils.JavaBeansUtil;
import cn.mysdp.utils.KeywordUtil;
import cn.mysdp.utils.SplitUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.checkerframework.checker.units.qual.A;
import org.mybatis.generator.api.ConnectionFactory;
import org.mybatis.generator.api.JavaTypeResolver;
import org.mybatis.generator.config.Context;
import org.mybatis.generator.config.JDBCConnectionConfiguration;
import org.mybatis.generator.config.PropertyRegistry;
import org.mybatis.generator.internal.JDBCConnectionFactory;
import org.mybatis.generator.internal.ObjectFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.sql.SQLNonTransientConnectionException;
import java.util.*;

import static cn.mysdp.biz.facade.ProcessSQLFacadeImpl.*;

/**
 * @ClassName:
 * @Description: FacadeCustomImpl
 * @Author: SDP
 * @Date: 2021-10-30
 * @Version: 1.0
 * Table:
 * Comment:
 *
 */
public class SdpSqlFacadeCustomImpl extends SdpSqlFacadeBaseImpl {

    @Autowired
    SdpSqlMapper sdpSqlMapper;

    @Autowired
    SdpWorkspaceConfigMapper sdpWorkspaceConfigMapper;

    @Autowired
    @Lazy
    ProcessSQLFacade processSQLFacade;

    private SdpSqlWithBLOBs parseComments(SdpWorkspaceWithBLOBs workspace,Map<String, String> tablesMap, SdpSqlAddRequest request, SdpSqlWithBLOBs paramItem, String[] tokens) throws Exception {
        return parseComments(workspace, tablesMap, request, paramItem, tokens, false);
    }

    private SdpSqlWithBLOBs parseComments(SdpWorkspaceWithBLOBs workspace,Map<String, String> tablesMap, SdpSqlAddRequest request, SdpSqlWithBLOBs paramItem, String[] tokens, boolean isCommentLine) throws Exception {
        for(int i = 1; i < tokens.length; i++) {
            String token = tokens[i].trim();
            String type = null;
            String remarks = null;
            Integer nullable = null;
            String catalog = null;
            String name = null;
            Integer isLike = null;
            Integer withoutTest = null;
            String imports = null;

            if(token.equals("interface") || token.equals("is interface") || token.equals("isInterface")) {
                if(isCommentLine) {
                    if(request.getIsInterface() == null) {
                        request.setIsInterface(1);
                    }
                    return paramItem;
                }
            }

            if(token.equals("no interface") || token.equals("not interface")
                    || token.equals("is no interface") || token.equals("is not interface")
                    || token.equals("isNoInterface")|| token.equals("isNotInterface")) {
                if(isCommentLine) {
                    if(request.getIsInterface() == null) {
                        request.setIsInterface(0);
                    }
                    return paramItem;
                }
            }
            if(token.equals("no list")
                    || token.equals("not list")
                    || token.equals("is not list")
                    || token.equals("is no list")
                    || token.equals("isNoList")
                    || token.equals("noList")
                    || token.equals("isNotList")
                    || token.equals("notList")) {
                if(isCommentLine) {
                    if(StringUtils.hasText(request.getExtraInfo())) {
                        JSONObject jsonObject = JavaBeansUtil.parseObject(request.getExtraInfo());
                        if(!jsonObject.containsKey("is_nolist") || "0".equals(jsonObject.getString("is_nolist"))) {
                            jsonObject.put("is_nolist", 1);
                            request.setExtraInfo(JavaBeansUtil.toJSONString(jsonObject));
                        }
                    } else {
                        request.setExtraInfo(JavaBeansUtil.toJSONString(JavaBeansUtil.parseObject("{is_nolist:1}")));
                    }
                    return paramItem;
                }
            }
            if(token.startsWith("return:")) {
                if(isCommentLine) {
                    if(!StringUtils.hasText(request.getJavaReturnType())) {
                        request.setJavaReturnType(token.substring("return:".length()).trim());
                    }

                    return paramItem;
                }
            }
            if(token.startsWith("result:")) {
                if(isCommentLine) {
                    if(!StringUtils.hasText(request.getJavaReturnType())) {
                        request.setJavaReturnType(token.substring("result:".length()).trim());
                    }

                    return paramItem;
                }
            }
            if(token.startsWith("name:")) {
                if(isCommentLine) {
                    if(!StringUtils.hasText(request.getParameterCatalogType())) {
                        request.setParameterCatalogType(JavaBeansUtil.getAjaxString((token.substring("name:".length()).trim())));
                        checkCatalogType(request.getParameterCatalogType());
                    }
                    return paramItem;
                }
            }
            if(token.startsWith("table:")) {
                if(isCommentLine) {
                    if(!StringUtils.hasText(request.getTableName())) {
                        String tname = JavaBeansUtil.getAjaxString((token.substring("table:".length()).trim()));
                        if(!tablesMap.containsKey(tname)) {
                            throw new Exception("table不存在:"+tname);
                        }
                        request.setTableName(tname);

                    }
                    return paramItem;
                }
            }
            if(token.startsWith("table name:")) {
                if(isCommentLine) {
                    if(!StringUtils.hasText(request.getTableName())) {
                        String tname = JavaBeansUtil.getAjaxString((token.substring("table name:".length()).trim()));
                        if(!tablesMap.containsKey(tname)) {
                            throw new Exception("table不存在:"+tname);
                        }
                        request.setTableName(JavaBeansUtil.getAjaxString((token.substring("table name:".length()).trim())));
                    }
                    return paramItem;
                }
            }
            if(token.equals("formdata")
                    || token.equals("form data")
                    || token.equals("FormData")
                    || token.equals("Formdata")
                    || token.equals("is formdata")
                    || token.equals("is form data")
                    || token.equals("is Formdata")
                    || token.equals("is FormData")
                    || token.equals("isFormData")
                    || token.equals("isFormdata")) {
                if(isCommentLine) {
                    if (StringUtils.hasText(request.getExtraInfo())) {
                        JSONObject jsonObject = JavaBeansUtil.parseObject(request.getExtraInfo());
                        jsonObject.put("is_formdata", 1);
                        request.setExtraInfo(JavaBeansUtil.toJSONString(jsonObject));
                    } else {
                        request.setExtraInfo(JavaBeansUtil.toJSONString(JavaBeansUtil.parseObject("{is_formdata:1}")));
                    }
                    return paramItem;
                }
            }
            if(token.startsWith("remarks:")) {
                remarks = token.substring("remarks:".length()).trim();
                if(isCommentLine) {
                    if(!StringUtils.hasText(request.getRemarks())) {
                        request.setRemarks(remarks);
                    }
                    return paramItem;
                }
            }
            if(token.startsWith("type:")) {
                type = token.substring("type:".length()).trim();
            }
            if(token.equals("not null")
                    || token.equals("is not null")
                    || token.equals("notNull")
                    || token.equals("isNotNull")
            ) {
                nullable = 0;
            }
            if(token.equals("null")
                    || token.equals("is null")
                    || token.equals("isNull")
            ) {
                nullable = 1;
            }
            if(token.equals("without test")
                    || token.equals("no test")
                    || token.equals("noTest")
                    || token.equals("isNoTest")
            ) {
                withoutTest = 1;
            }
            if(token.equals("like")
                    || token.equals("is like")
            ) {
                isLike = 1;
            }
            if(token.equals("not like")
                    || token.equals("is not like")
            ) {
                isLike = 0;
            }
            if(token.startsWith("import:")) {
                imports = token.substring("import:".length()).trim();
                if(isCommentLine) {
                    if(!StringUtils.hasText(request.getJavaImports())) {
                        request.setJavaImports(imports);
                    } else if(!request.getJavaImports().contains(imports)){
                        request.setJavaImports(request.getJavaImports()+System.lineSeparator()+imports);
                    }
                    return paramItem;
                }
            }
            if(token.startsWith("name:")) {
                name = token.substring("name:".length()).trim();
                if(!name.equals(name.toLowerCase())) {
                    throw new Exception("名称必须为小写下划线规则:"+token);
                }
            }
            if(token.equals("request")) {
                catalog = "sql.param";
            }
            if(token.equals("response")) {
                catalog = "sql.response";
            }
            if(type == null) {
                if("Integer".equals(token)
                        || "String".equals(token)
                        || "Boolean".equals(token)
                        || "Double".equals(token)
                        || "Float".equals(token)
                        || "Date".equals(token)
                        || "MultipartFile".equals(token)
                        || "BigDecimal".equals(token)
                        || token.indexOf("List<") == 0
                        || token.indexOf("Set<") == 0
                        || token.indexOf("Map<") == 0
                        || token.indexOf("Integer[") == 0
                        || token.indexOf("Integer [") == 0
                        || token.indexOf("Double[") == 0
                        || token.indexOf("Double [") == 0
                        || token.indexOf("Float[") == 0
                        || token.indexOf("Float [") == 0
                        || token.indexOf("BigDecimal[") == 0
                        || token.indexOf("BigDecimal [") == 0
                        || token.indexOf("Date[") == 0
                        || token.indexOf("Date [") == 0
                        || token.indexOf("String[") == 0
                        || token.indexOf("String [") == 0
                        || token.indexOf("Boolean[") == 0
                        || token.indexOf("Boolean [") == 0
                ) {
                    type = token;
                }
            }

            if(type != null && type.contains("MultipartFile")) {
                String _imports = "import org.springframework.web.multipart.MultipartFile;";
                if(!StringUtils.hasText(imports)) {
                    imports = _imports;
                } else if(!imports.contains(_imports)) {
                    imports = imports + System.lineSeparator() + _imports;
                }
                if(StringUtils.hasText(request.getExtraInfo())) {
                    JSONObject jsonObject = JavaBeansUtil.parseObject(request.getExtraInfo());
                    if(!jsonObject.containsKey("is_formdata")) {
                        jsonObject.put("is_formdata", 1);
                    }
                    if(!jsonObject.containsKey("is_nolist")) {
                        jsonObject.put("is_nolist", 1);
                    }
                    request.setExtraInfo(JavaBeansUtil.toJSONString(jsonObject));
                } else {
                    request.setExtraInfo(JavaBeansUtil.toJSONString(JavaBeansUtil.parseObject("{is_formdata:1,is_nolist:1}")));
                }
                if(request.getIsInterface() == null) {
                    request.setIsInterface(1);
                }
            }

            if(type != null || remarks != null || nullable != null || catalog != null || name != null || isLike != null || withoutTest != null || imports != null) {
                if(paramItem == null) {
                    paramItem = new SdpSqlWithBLOBs();
                    paramItem.setWorkspaceName(request.getWorkspaceName());
                    paramItem.setTableName(request.getTableName());
                    paramItem.setParameterCatalogType(request.getParameterCatalogType());
                    paramItem.setProjectNames(request.getProjectNames());
                }
                if(imports != null) {
                    if(!StringUtils.hasText(request.getJavaImports())) {
                        paramItem.setJavaImports(imports);
                    } else if(!request.getJavaImports().contains(imports)){
                        paramItem.setJavaImports(paramItem.getJavaImports()+System.lineSeparator()+imports);
                    }
                    if(!StringUtils.hasText(request.getJavaImports())) {
                        request.setJavaImports(imports);
                    } else if(!request.getJavaImports().contains(imports)){
                        request.setJavaImports(request.getJavaImports()+System.lineSeparator()+imports);
                    }
                }
                if(withoutTest != null) {
                    paramItem.setParameterWithoutTest(withoutTest);
                    if(!StringUtils.hasText(paramItem.getParameterCatalog())) {
                        paramItem.setParameterCatalog("sql.param");
                    }
                }
                if(name != null) {
                    paramItem.setName(name);
                }
                if(isLike != null) {
                    paramItem.setParameterIsLike(isLike);
                    if(!StringUtils.hasText(paramItem.getParameterCatalog())) {
                        paramItem.setParameterCatalog("sql.param");
                    }
                }
                if(catalog != null) {
                    paramItem.setParameterCatalog(catalog);
                }
                if(type != null) {
                    paramItem.setJavaType(type);
                }
                if(remarks != null) {
                    paramItem.setRemarks(remarks);
                }
                if(nullable != null) {
                    paramItem.setParameterNullable(nullable);
                    if(!StringUtils.hasText(paramItem.getParameterCatalog())) {
                        paramItem.setParameterCatalog("sql.param");
                    }
                }
            }
        }
        return paramItem;
    }

    private void checkCatalogType(String name) throws Exception {
        if(StringUtils.hasText(name)) {
            if(KeywordUtil.isKeyword(name, "java")) {
                throw new Exception("禁止使用java保留字作为方法名称:"+name);
            }
        }
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public Integer addSdpSql(SdpSqlAddRequest request) throws Exception {
        checkLowercaseName(request.getWorkspaceName());
        checkLowercaseName(request.getTableName());
        checkLowercaseNameWithDot(request.getParameterCatalog());
        checkLowercaseName(request.getParameterCatalogType());
        checkLowercaseName(request.getName());

        checkCatalogType(request.getParameterCatalogType());

        if (StringUtils.isEmpty(request.getWorkspaceName())) {
            throw new Exception("miss parameter：workspace_name");
        }

        if (StringUtils.isEmpty(request.getParameterCatalog())) {
            throw new Exception("miss parameter：catalog");
        }

        String workspaceName = request.getWorkspaceName();

        SdpWorkspaceWithBLOBs workspace = sdpWorkspaceMapper.selectByNameWithBLOBs(request.getWorkspaceName());
        if(workspace == null) {
            throw new Exception("workspace不存在："+request.getWorkspaceName());
        }
        Map<String, String> tablesMap = new HashMap<>();
        {
            SdpProjectExample example = new SdpProjectExample();
            example.createCriteria().andWorkspaceNameEqualTo(request.getWorkspaceName());
            List<SdpProjectWithBLOBs> projects = sdpProjectMapper.selectByExampleWithBLOBs(example);
            for(SdpProjectWithBLOBs project: projects) {
                if(StringUtils.hasText(project.getTables())) {
                    String[] tables = project.getTables().split(",");
                    for(String table: tables) {
                        table = table.trim();
                        table = table.split("-")[0].trim();
                        if(table.contains(" as ")) {
                            String[] ts = table.split(" as ");
                            tablesMap.put(ts[1].trim(), ts[0].trim());
                        } else {
                            tablesMap.put(table, table);
                        }
                    }
                }
            }
        }

        String sqlOnlyOneBlank = "";
        if(StringUtils.hasText(request.getParameterSql())) {
            sqlOnlyOneBlank = request.getParameterSql().replaceAll("\t", " ");
            while(sqlOnlyOneBlank.contains("  ")){
                sqlOnlyOneBlank = sqlOnlyOneBlank.replaceAll("  ", " ");
            }

            List<String> lines = SplitUtil.splitLines(sqlOnlyOneBlank);
            for(String line:lines) {
                String sql = line.trim();
                {
                    String[] commentTokens = sql.split("-- ");
                    for(String commentToken: commentTokens) {
                        commentToken = commentToken.trim();
                        SdpSqlWithBLOBs paramItem = parseComments(workspace, tablesMap, request, null, new String[]{"", commentToken}, true);
                    }
                }
            }
        }

        if(StringUtils.hasText(request.getJavaReturnType())) {
            if(StringUtils.hasText(request.getExtraInfo())) {
                JSONObject jsonObject = JavaBeansUtil.parseObject(request.getExtraInfo());
                if(jsonObject.containsKey("is_nolist")) {
                    jsonObject.remove("is_nolist");
                    request.setExtraInfo(JavaBeansUtil.toJSONString(jsonObject));
                }
            }
        }

        if (StringUtils.isEmpty(request.getTableName())) {
            throw new Exception("miss parameter：table_name");
        }
        if (StringUtils.isEmpty(request.getParameterCatalogType())) {
            throw new Exception("miss parameter：catalog_type");
        }

        if("sql".equals(request.getParameterCatalog())) {
            request.setName("");
        }
        if (!"sql".equals(request.getParameterCatalog())) {
            return super.addSdpSql(request);
        }
        if (StringUtils.isEmpty(request.getParameterSql())) {
            throw new Exception("miss parameter：sql");
        }

        if (StringUtils.hasText(request.getJavaReturnType())) {
            for(String name: new String[]{"int", "long", "float", "double"}) {
                if (name.equals(request.getJavaReturnType())) {
                    throw new Exception("invalid return type, cannot be base type:"+request.getJavaReturnType());
                }
            }
        }

        if(StringUtils.hasText(request.getTableName())) {
            request.setTableName(request.getTableName().split("-")[0]);
        }

        if("api.facade".equals(request.getParameterCatalog())) {
            if(!"request".equals(request.getName())) {
                throw new Exception("api.facade类型的name必须设置request");
            }
        }

        Map<String, String> configMap = new HashMap<>();
        {
            SdpWorkspaceConfigExample example = new SdpWorkspaceConfigExample();
            example.createCriteria().andWorkspaceNameEqualTo(request.getWorkspaceName());
            List<SdpWorkspaceConfigWithBLOBs> configList = sdpWorkspaceConfigMapper.selectByExampleWithBLOBs(example);
            for(SdpWorkspaceConfigWithBLOBs item: configList) {
                configMap.put(item.getName(), item.getValue());
            }
        }

        if (!Integer.valueOf(1).equals(request.getDisableAutoParam()) && StringUtils.hasText(request.getParameterSql())) {
            String onSpaceLowerSql = request.getParameterSql().toLowerCase().replaceAll("\n", " ")
                    .replaceAll("\t", " ")
                    .replaceAll("\r", " ")
                    .replaceAll(" select ", ",select,")
                    .replaceAll(" from ", ",from ")
                    .replaceAll("  ", " ")
                    .replaceAll(", ", ",")
                    .replaceAll(" ,", ",");
            if(onSpaceLowerSql.startsWith("select ")) {
                onSpaceLowerSql = ",select,"+onSpaceLowerSql.substring("select ".length());
            }

            SdpSqlExample example = new SdpSqlExample();
            example.createCriteria()
                    .andWorkspaceNameEqualTo(request.getWorkspaceName())
                    .andTableNameEqualTo(request.getTableName())
                    .andParameterCatalogEqualTo(request.getParameterCatalog())
                    .andParameterCatalogTypeEqualTo(request.getParameterCatalogType());
            List<SdpSqlWithBLOBs> list = sdpSqlMapper.selectByExampleWithBLOBs(example);
            if (!CollectionUtils.isEmpty(list)) {
                return super.addSdpSql(request);
            }

            Map<String, Map<String, String>> fieldsInfoMap = new HashMap<>();
            Set<String> doneNameSet = new HashSet<>();

            {
                Connection connection = processSQLFacade.getConnection(workspace);


                PreparedStatement mysqlStatement = null;
                try {
                    String sql = request.getParameterSql();

                    try {
                        mysqlStatement = connection.prepareStatement(sql);
                    } catch (Exception ex) {
                        if((ex.getMessage()+"").contains("This connection has been closed")) {
                            processSQLFacade.closeConnection(workspace);
                        }
                        System.err.println("=== prepareStatement error, sql ===");
                        System.err.println(sql);
                        throw ex;
                    }
                    ResultSetMetaData resultSetMetaData;
                    try {
                        resultSetMetaData = mysqlStatement.getMetaData();
                    } catch (Exception ex) {
                        System.err.println("=== getMetaData error, sql ===");
                        System.err.println(sql);
                        throw new Exception(ex.getMessage());
                    }


                    Object[] jdbcFields = ConnectUtil.getMetaFields(resultSetMetaData);
                    int oriColumnCount = jdbcFields.length;

                    Map<String, Method> methodMap = new HashMap<>();
                    methodMap.put("getName", null);
                    methodMap.put("getTableName", null);
                    methodMap.put("getOriginalTableName", null);
                    methodMap.put("getColumnName", null);
                    methodMap.put("getFullName", null);
                    methodMap.put("getOriginalColumnName", null);
                    methodMap.put("getDatabaseName", null);

                    Map<String, Field> fieldMap = new HashMap<>();

                    Class clz = null;
                    if (jdbcFields.length > 0) {
                        clz = jdbcFields[0].getClass();
                        checkSingleStar(sql);
                    }
                    while (clz != null) {
                        int count = 0;
                        Method[] methods = jdbcFields[0].getClass().getDeclaredMethods();
                        Field[] fields = jdbcFields[0].getClass().getDeclaredFields();
                        for(Field field: fields) {
                            try {
                                if(!fieldMap.containsKey(field.getName()) || fieldMap.get(field.getName()) == null) {
                                    field.setAccessible(true);
                                    fieldMap.put(field.getName(), field);
                                }
                            }catch (Exception ex) {

                            }
                        }
                        for (Method method : methods) {
                            count = 0;
                            for (String fieldKey : methodMap.keySet()) {
                                if (methodMap.get(fieldKey) != null) {
                                    count++;
                                    continue;
                                }
                                if (fieldKey.equals(method.getName())) {
                                    method.setAccessible(true);
                                    methodMap.put(fieldKey, method);
                                    count++;
                                }
                            }
                            if (count == methodMap.size()) {
                                break;
                            }
                        }
                        if (count == methodMap.size()) {
                            break;
                        }
                        clz = clz.getSuperclass();
                    }

                    if (jdbcFields.length > 0) {
                        clz = jdbcFields[0].getClass();
                    }
                    while (clz != null) {
                        int count = 0;
                        Field[] fields = jdbcFields[0].getClass().getDeclaredFields();
                        for (Field field : fields) {
                            try {
                                if(!fieldMap.containsKey(field.getName()) || fieldMap.get(field.getName()) == null) {
                                    field.setAccessible(true);
                                    fieldMap.put(field.getName(), field);
                                }
                            }catch (Exception ex) {

                            }
                        }
                        clz = clz.getSuperclass();
                    }

                    for (Object field : jdbcFields) {
                        Map<String, String> fieldInfoMap = new HashMap<>();
//                        String fieldOriginalTableName = null;
//                        if(fieldMap.get("originalTableName") != null) {
//                            fieldOriginalTableName = (String) fieldMap.get("originalTableName").get(field);
//                            fieldInfoMap.put("originalTableName", fieldOriginalTableName);
//                        }
//                        if(fieldMap.get("originalColumnName") != null) {
//                            fieldInfoMap.put("originalColumnName", (String)fieldMap.get("originalColumnName").get(field));
//                        }
//                        if(fieldMap.get("name") != null) {
//                            fieldInfoMap.put("name", (String) fieldMap.get("name").get(field));
//                        }
//                        if(fieldMap.get("tableName") != null) {
//                            fieldInfoMap.put("tableName", (String) fieldMap.get("tableName").get(field));
//                        }
//                        if(fieldMap.get("columnName") != null) {
//                            fieldInfoMap.put("columnName", (String) fieldMap.get("columnName").get(field));
//                        }
//                        if(fieldMap.get("databaseName") != null) {
//                            fieldInfoMap.put("databaseName", (String) fieldMap.get("databaseName").get(field));
//                        }

                        for(String fieldName: fieldMap.keySet()) {
                            if (!StringUtils.hasText(fieldInfoMap.get(fieldName)) && fieldMap.get(fieldName) != null) {
                                Object v = fieldMap.get(fieldName).get(field);
                                if(v != null) {
                                    if("mysqlType".equals(fieldName)) {
                                        Field f = JavaBeansUtil.getField(v.getClass(), "javaClass");
                                        if(f != null) {
                                            Object _c = f.get(v);
                                            if(_c instanceof Class) {
                                                Class _c2 = (Class) _c;
                                                fieldInfoMap.put("javaClass", _c2.getName());
                                                fieldInfoMap.put("javaClassSimpleName", _c2.getSimpleName());
                                                fieldInfoMap.put("javaClassCanonicalName", _c2.getCanonicalName());
                                                fieldInfoMap.put("javaClassTypeName", _c2.getTypeName());
                                            }
                                        }
                                    }
                                    fieldInfoMap.put(fieldName, v.toString());
                                }
                            }
                        }

                        if(fieldInfoMap.containsKey("columnLabel") && !fieldInfoMap.containsKey("columnName")) {
                            fieldInfoMap.put("columnName", fieldInfoMap.get("columnLabel"));
                        }
                        if(fieldInfoMap.containsKey("name") && !fieldInfoMap.containsKey("columnName")) {
                            fieldInfoMap.put("columnName", fieldInfoMap.get("name"));
                        }
                        if(!fieldInfoMap.containsKey("columnName")) {
                            throw new Exception("无法分析columnName");
                        }
                        doneNameSet.add(fieldInfoMap.get("columnName"));
                        fieldsInfoMap.put(fieldInfoMap.get("columnName"), fieldInfoMap);
                    }

                } catch (Exception ex) {
                    ex.printStackTrace();
                    throw ex;
                } finally {
                    if (mysqlStatement != null) {
                        mysqlStatement.close();
                    }
                }
//                connection.close();
            }

            String[] ops = new String[]{
                    " like ,_like",
                    " not like ,_not_like",
                    " in ,_in",
                    " not in ,_not_in",
                    ">=,_ge",
                    "<=,_le",
                    "<>,_not_eq",
                    "!=,_not_eq",
                    "between,_start",
                    " regexp ,_regexp",
                    ">,_gt",
                    "<,_lt",
                    "=,"};

            if(CollectionUtils.isEmpty(doneNameSet)) {
                String[] lines = request.getParameterSql().split("\n");
                for(String line: lines) {
                    String name = null;
                    if(line.contains("-- param")
                            || line.contains("-- request")
                            || line.contains("-- null")
                            || line.contains("-- not null")
                            || line.contains("-- no test")
                            || line.contains("-- like")
                            || line.contains("-- MultipartFile")
                            || line.contains("-- type:")
                            || line.contains("-- Integer")
                            || line.contains("-- Long")
                            || line.contains("-- Double")
                            || line.contains("-- BigDecimal")
                            || line.contains("-- List")
                            || line.contains("-- JSONObject")
                            || line.contains("-- JSONArray")
                            || line.contains("-- Map")
                            || line.contains("-- Set")
                            || line.contains("-- Float")
                            || line.contains("-- String")
                            || line.contains("-- Date")
                            || line.contains("-- import")
                            || line.contains("-- response")) {
                        if(line.contains("-- name:")) {
                            name = line.split("-- name:")[1].split(" ")[0];
                            if(!name.equals(name.toLowerCase())) {
                                throw new Exception("名称必须为小写英文:"+name);
                            }
                        } else {
                            if(line.startsWith("and ")) {
                                for (String opInfo : ops) {
                                    String op;
                                    String opEnd = "";
                                    String[] opInfos = SplitUtil.split(opInfo, ",");
                                    try {
                                        op = opInfos[0];
                                        if (opInfos.length > 1) {
                                            opEnd = opInfos[1];
                                        }
                                    } catch (Exception ex) {
                                        ex.printStackTrace();
                                        throw ex;
                                    }
                                    int opPos = line.toLowerCase().indexOf(op);
                                    if (opPos <= 0) {
                                        continue;
                                    }
                                    String left = line.substring(0, opPos).toLowerCase().trim();
                                    String right = line.substring(opPos + op.length()).trim();
                                    if (StringUtils.isEmpty(left) || StringUtils.isEmpty(right)) {
                                        continue;
                                    }
                                    name = left;
                                    if(!name.equals(name.toLowerCase())) {
                                        throw new Exception("名称必须为小写英文:"+name);
                                    }
                                }
                            }
                        }
                        if(StringUtils.hasText(name)) {
                            doneNameSet.add(name);
                        }
                    }
                }
            }

            Map<String, SdpSqlWithBLOBs> paramMap = new HashMap<>();

            String lowerSql = request.getParameterSql().toLowerCase();

            int wherePos = lowerSql.indexOf("where ");
            if(wherePos < 0) {
                wherePos = lowerSql.indexOf("where\n");
            }
            int fromPos = onSpaceLowerSql.indexOf(",from ");
            if(!CollectionUtils.isEmpty(doneNameSet)) {
                if(wherePos < 0 && fromPos < 0) {
                    request.setIsInterface(1);
                }
                for(String columnName: doneNameSet) {
                    SdpSqlWithBLOBs paramItem = null;
                    if(lowerSql.contains(columnName.toLowerCase()) && !request.getParameterSql().contains(columnName.toLowerCase())) {
                        throw new Exception("字段名必须为小写下划线规则："+columnName);
                    }

                    for(String sp : new String[]{" " + columnName+",", " " + columnName+" ,", " `" + columnName+"`,", " `" + columnName+"`, ", " " + columnName + "\n", " " + columnName + " \n", " `" + columnName + "`\n", " `" + columnName + "` \n", " " + columnName + " -- ", " `" + columnName + "` -- "}) {
                        String sql = sqlOnlyOneBlank;
                        while(sql.indexOf(sp) > 0) {
                            String thisSql = sql.substring(sql.indexOf(sp)).split("\n")[0];
                            sql = sql.substring(sql.indexOf(sp)+sp.length());
                            String[] tokens = thisSql.split("-- ");
                            for(int i = 1; i < tokens.length; i++) {
                                paramItem = parseComments(workspace, tablesMap, request, paramItem, new String[]{"", tokens[i]});
                            }
                        }
                    }
                    if(paramItem != null) {
                        if(paramItem.getName() == null) {
                            paramItem.setName(columnName);
                        }
                        if(!paramItem.getName().equals(paramItem.getName().toLowerCase())){
                            throw new Exception("字段名必须为小写下划线命名规则:"+paramItem.getName());
                        }
                        if(!StringUtils.hasText(paramItem.getJavaType())) {
                            Map<String, String> info = fieldsInfoMap.get(columnName);
                            paramItem.setJavaType(info.get("javaClassSimpleName"));
                        }
                        paramMap.put(paramItem.getName(), paramItem);
                    }
                }
            }

            if (wherePos > 0 && !CollectionUtils.isEmpty(doneNameSet)) {
                wherePos = request.getParameterSql().toLowerCase().indexOf("where ");
                if(wherePos < 0) {
                    wherePos = request.getParameterSql().toLowerCase().indexOf("where\n");
                }
                String where = request.getParameterSql().substring(wherePos);
                lowerSql = where.toLowerCase();
                //            for(String endString: new String[]{"order by ", "group by ", "limit "}) {
                //                int endWherePos = lowerSql.indexOf(endString);
                //                if (endWherePos > 0) {
                //                    where = where.substring(0, endWherePos);
                //                    lowerSql = where.toLowerCase();
                //                }
                //            }
                List<String> lines = SplitUtil.splitLines(where);

                Map<String, Integer> doneName = new HashMap<>();
                String firstTable = "";
                for (String line : lines) {
                    line = line.trim();
                    String[] forceTokens = line.split("'#\\{");
                    if (!line.startsWith("and ") && !line.startsWith("and(") && forceTokens.length < 2) {
                        continue;
                    }
                    String lineStart = line.substring(0, 10);

                    SdpSqlWithBLOBs paramItem = new SdpSqlWithBLOBs();
                    paramItem.setWorkspaceName(request.getWorkspaceName());
                    paramItem.setTableName(request.getTableName());
                    paramItem.setParameterCatalog("sql.param");
                    paramItem.setParameterCatalogType(request.getParameterCatalogType());
                    paramItem.setProjectNames(request.getProjectNames());

                    Map<String, String> lineCommentMap = new HashMap<>();
                    String[] commentTokens = line.split("-- ");
                    if (line.indexOf("-- ") >= 0) {
                        paramItem = parseComments(workspace, tablesMap, request, paramItem, commentTokens);
                    }
                    line = line.substring(3).split("-- ")[0].trim();

                    if(forceTokens.length > 1) {
                        for(int forceN = 1; forceN < forceTokens.length; forceN++) {
                            String[] ts = forceTokens[forceN].trim().split("}'");
                            if(ts.length > 1) {
                                String t = ts[0].trim();
                                String ajaxT = JavaBeansUtil.getAjaxString(t);
                                SdpSqlWithBLOBs newParamItem = new SdpSqlWithBLOBs();
                                BeanUtils.copyProperties(paramItem, newParamItem);
                                newParamItem.setName(ajaxT);
                                for(String commentToken: commentTokens) {
                                    commentToken = commentToken.trim();
                                    if(commentToken.startsWith("("+t+")")) {
                                        newParamItem = parseComments(workspace, tablesMap, request, newParamItem, new String[]{"", commentToken.substring(("("+t+")").length())});
                                    }
                                    if(commentToken.startsWith("("+ajaxT+")")) {
                                        newParamItem = parseComments(workspace, tablesMap, request, newParamItem, new String[]{"", commentToken.substring(("("+ajaxT+")").length())});
                                    }
                                }
                                doneNameSet.add(newParamItem.getName());
                                if(newParamItem.getJavaType() == null) {
                                    newParamItem.setJavaType("String");
                                }
                                if(newParamItem.getParameterNullable() == null) {
                                    newParamItem.setParameterNullable(1);
                                }
                                newParamItem.setParameterSqlValue("'#{"+t+"}'");
                                paramMap.put(newParamItem.getName(), newParamItem);
                            }
                        }
                        continue;
                    }
                    int leftBrackets = 0;
                    while (line.startsWith("(")) {
                        leftBrackets++;
                        line = line.substring(1).trim();
                    }
                    while (leftBrackets > 0 && line.endsWith(")")) {
                        line = line.substring(0, line.length() - 1).trim();
                    }

                    String[] rights = null;
                    for (String opInfo : ops) {
                        String op;
                        String opEnd = "";
                        String[] opInfos = SplitUtil.split(opInfo, ",");
                        try {
                            op = opInfos[0];
                            if (opInfos.length > 1) {
                                opEnd = opInfos[1];
                            }
                        } catch (Exception ex) {
                            ex.printStackTrace();
                            throw ex;
                        }
                        int opPos = line.toLowerCase().indexOf(op);
                        if (opPos <= 0) {
                            continue;
                        }
                        String left = line.substring(0, opPos).toLowerCase().trim();
                        String right = line.substring(opPos + op.length()).trim();
                        if (StringUtils.isEmpty(left) || StringUtils.isEmpty(right)) {
                            continue;
                        }

                        String validChars = "abcdefghijklmnopqrstuvwxyz1234567890_";
                        String newLeft = "";
                        char lastChar = ' ';
                        for (char c : left.toCharArray()) {
                            if (c == '\'') {
                                break;
                            }
                            if (c == '.') {
                                if ("".equals(firstTable)) {
                                    firstTable = newLeft;
                                    newLeft = "";
                                    lastChar = ' ';
                                    continue;
                                }
                                if (firstTable.equals(newLeft)) {
                                    newLeft = "";
                                    lastChar = ' ';
                                    continue;
                                }
                                if (lastChar != '_') {
                                    newLeft += "_";
                                    lastChar = '_';
                                }
                                continue;
                            }
                            if (validChars.indexOf(c) < 0) {
                                if (lastChar != '_') {
                                    newLeft += "_";
                                    lastChar = '_';
                                }
                                continue;
                            }
                            newLeft += c;
                            lastChar = c;
                        }
                        if (StringUtils.isEmpty(newLeft)) {
                            continue;
                        }
                        if (newLeft.toCharArray()[0] >= '0' && newLeft.toCharArray()[0] <= '9') {
                            continue;
                        }
                        left = newLeft;

                        if("between".equals(op)) {
                            rights = right.split(" and ");
                            if(rights.length < 1) {
                                continue;
                            }
                            paramItem.setParameterSqlValue(rights[0]);
                        } else {
                            paramItem.setParameterSqlValue(right);
                        }

                        if(paramItem.getParameterNullable() == null) {
                            paramItem.setParameterNullable(1);
                        }
                        JSONObject extraInfo = new JSONObject();

                        boolean done = false;
                        if (" in ".equals(op) || " not in ".equals(op)) {
                            if (right.startsWith("(") && right.endsWith(")")) {
                                right = right.substring(1);
                                right = right.substring(0, right.length() - 1);
                                right = right.split(",")[0];
                            }
                        }

                        if (right.length() >= 2 && right.startsWith("'") && right.endsWith("'")) {
                            if (right.length() >= 10 && right.length() <= 12 && right.split("-").length == 3) {
                                if(paramItem.getJavaType() == null) {
                                    paramItem.setJavaType("Date");
                                }
                            } else if (right.length() >= 19 && right.length() <= 21 && right.split("-").length == 3 && right.split(":").length == 3) {
                                if(paramItem.getJavaType() == null) {
                                    paramItem.setJavaType("Date");
                                }
                            } else {
                                if (" like ".equals(op)
                                        || " not like ".equals(op)
                                ) {
                                    if ("1".equals(configMap.get("analyse_sql_param_like_set_extra_info"))) {
                                        extraInfo.put("is_like", "1");
                                    } else {
                                        if(paramItem.getParameterIsLike() == null) {
                                            paramItem.setParameterIsLike(1);
                                        }
                                    }
                                    if ("0".equals(configMap.get("analyse_sql_param_like_append_to_name"))) {
                                        opEnd = "";
                                    }
                                }
                                if(paramItem.getJavaType() == null) {
                                    paramItem.setJavaType("String");
                                }
                            }
                            done = true;
                        }
                        if (StringUtils.isEmpty(paramItem.getJavaType())) {
                            if (right.startsWith("'")) {
                                if(paramItem.getJavaType() == null) {
                                    paramItem.setJavaType("String");
                                }
                            } else {
                                if(paramItem.getJavaType() == null) {
                                    paramItem.setJavaType("Integer");
                                }
                            }
                        }
                        if (" in ".equals(op) || " not in ".equals(op)) {
                            paramItem.setJavaType("List<" + paramItem.getJavaType() + ">");
                            extraInfo.put("is_collection", "1");
                        }
                        left += opEnd;
                        if (left.split("\\.").length > 1) {
                            left = left.split("\\.")[left.split("\\.").length - 1];
                        }
                        left = left.replaceAll(" ", "_");
                        String _left = left;
                        while(true) {
                            if (doneName.containsKey(_left)) {
                                if(!doneName.containsKey(left)) {
                                    doneName.put(left, 1);
                                }
                                doneName.put(left, doneName.get(left) + 1);
                                _left += doneName.get(left) + 1;
                            }
                            if(!doneName.containsKey(_left)) {
                                doneName.put(_left, 1);
                                break;
                            }
                            doneName.put(_left, 1);
                        }
                        left = _left;
                        doneName.put(left, 1);
                        doneNameSet.add(left);
                        if(paramItem.getName() == null) {
                            paramItem.setName(left);
                        }
                        if (fieldsInfoMap.containsKey(left)) {
                            Map<String, String> fieldInfoMap = fieldsInfoMap.get(left);
                            String _t = fieldInfoMap.get("javaClassSimpleName");
                            if (StringUtils.hasText(_t)) {
                                if(paramItem.getJavaType() == null) {
                                    paramItem.setJavaType(_t);
                                }
                            }
                        }
                        if (!CollectionUtils.isEmpty(extraInfo.keySet())) {
                            paramItem.setExtraInfo(JavaBeansUtil.toJSONString(extraInfo));
                        }
                        paramMap.put(paramItem.getName(), paramItem);
                        if("between".equals(op) && rights != null) {
                            SdpSqlWithBLOBs newParam = new SdpSqlWithBLOBs();
                            BeanUtils.copyProperties(paramItem, newParam);
                            boolean found = false;
                            for(String start: new String[]{"_start", "start_"}) {
                                if(newParam.getName().contains(start)) {
                                    newParam.setName(newParam.getName().replace("start_", "end_").replace("_start", "_end"));
                                    found = true;
                                    break;
                                }
                            }
                            if(!found) {
                                newParam.setName(newParam.getName()+"_end");
                            }
                            if(lineStart.startsWith("and (((") && rights[1].endsWith(")))")) {
                                rights[1] = rights[1].substring(0, rights[1].length() - 3);
                            } else if(lineStart.startsWith("and ((") && rights[1].endsWith("))")) {
                                rights[1] = rights[1].substring(0, rights[1].length() - 2);
                            } else if(lineStart.startsWith("and (") && rights[1].endsWith(")")) {
                                rights[1] = rights[1].substring(0, rights[1].length() - 1);
                            }
                            newParam.setParameterSqlValue(rights[1]);
                            paramMap.put(newParam.getName(), newParam);
                        }
                        break;
                    }
                }
            }

            if(!CollectionUtils.isEmpty(doneNameSet)) {
                List<String> lines = SplitUtil.splitLines(sqlOnlyOneBlank);
                for(String line:lines) {
                    String sql = line.trim();
                    if(sql.startsWith("-- (")) {
                        sql = sql.substring("-- (".length());
                        if(sql.indexOf(")") > 0) {
                            String t = sql.substring(0, sql.indexOf(")"));
                            String ajaxT = JavaBeansUtil.getAjaxString(t);

                            sql = sql.substring(sql.indexOf(")")+1);

                            SdpSqlWithBLOBs paramItem = paramMap.get(t);
                            if(paramItem == null) {
                                paramItem = paramMap.get(ajaxT);
                            }
                            if(paramItem != null) {
                                String[] commentTokens = sql.split("-- ");
                                for(String commentToken: commentTokens) {
                                    commentToken = commentToken.trim();
                                    paramItem = parseComments(workspace, tablesMap, request, paramItem, new String[]{"", commentToken});
                                }
                            }
                        }
                    }
                }
            }


            Set<String> addedItemSet = new HashSet<>();
            for(String name: paramMap.keySet()) {
                SdpSqlWithBLOBs paramItem = paramMap.get(name);
                if(!addedItemSet.contains(paramItem.getName())) {
                    if(!StringUtils.hasText(paramItem.getParameterCatalog())) {
                        paramItem.setParameterCatalog("sql.response");
                    }
                    if("sql.param".equals(paramItem.getParameterCatalog())) {
                        if(!StringUtils.hasText(paramItem.getParameterSqlValue())) {
                            paramItem.setParameterWithoutTest(1);
                            paramItem.setParameterSqlValue("88");
                            paramItem.setParameterSqlValueIgnore(1);
                        }
                    }
                    sdpSqlMapper.insertSelective(paramItem);
                    addedItemSet.add(paramItem.getName());
                }
            }


            if(!CollectionUtils.isEmpty(doneNameSet)) {
                List<String> lines = SplitUtil.splitLines(request.getParameterSql());

                for(String line :lines) {
                    line = line.trim();
                    if (!line.startsWith("top ") && !line.startsWith("limit ")) {
                        continue;
                    }
                    line = line.split("-- ")[0].trim();

                    String[] words = new String[]{line.split(" ")[0]};
                    for(String opInfo:words) {
                        String left = opInfo;
                        String right = line.substring(opInfo.length()).trim();
                        if (StringUtils.isEmpty(left) || StringUtils.isEmpty(right)) {
                            continue;
                        }

                        if("limit".equals(opInfo) && right.contains(",")) {
                            String[] limits = right.split(",");
                            String offset = limits[0].trim();
                            String count = limits[1].trim();
                            if(StringUtils.hasText(offset) && StringUtils.hasText(count)) {
                                left = "offset";
                                while(doneNameSet.contains(left)) {
                                    left = "_"+left;
                                }
                                right = offset;
                                doneNameSet.add(left);

                                SdpSqlWithBLOBs paramItem = new SdpSqlWithBLOBs();
                                paramItem.setWorkspaceName(request.getWorkspaceName());
                                paramItem.setTableName(request.getTableName());
                                paramItem.setParameterCatalog("sql.param");
                                paramItem.setParameterCatalogType(request.getParameterCatalogType());
                                paramItem.setParameterSqlValue(right);
                                paramItem.setParameterNullable(1);
                                JSONObject extraInfo = new JSONObject();

                                paramItem.setJavaType("Integer");
                                paramItem.setName(left);
                                paramItem.setProjectNames(request.getProjectNames());
                                if(!CollectionUtils.isEmpty(extraInfo.keySet())) {
                                    paramItem.setExtraInfo(JavaBeansUtil.toJSONString(extraInfo));
                                }
                                sdpSqlMapper.insertSelective(paramItem);

                                left = "limit";
                                while(doneNameSet.contains(left)) {
                                    left = "_"+left;
                                }
                                doneNameSet.add(left);
                                paramItem.setName(left);
                                paramItem.setParameterSqlValue(count);
                                paramItem.setId(null);
                                sdpSqlMapper.insertSelective(paramItem);
                                break;
                            }
                        }

                        while(doneNameSet.contains(left)) {
                            left = "_"+left;
                        }
                        doneNameSet.add(left);

                        SdpSqlWithBLOBs paramItem = new SdpSqlWithBLOBs();
                        paramItem.setWorkspaceName(request.getWorkspaceName());
                        paramItem.setTableName(request.getTableName());
                        paramItem.setParameterCatalog("sql.param");
                        paramItem.setParameterCatalogType(request.getParameterCatalogType());
                        paramItem.setParameterSqlValue(right);
                        paramItem.setParameterNullable(1);
                        JSONObject extraInfo = new JSONObject();

                        paramItem.setJavaType("Integer");
                        paramItem.setName(left);
                        paramItem.setProjectNames(request.getProjectNames());
                        if(!CollectionUtils.isEmpty(extraInfo.keySet())) {
                            paramItem.setExtraInfo(JavaBeansUtil.toJSONString(extraInfo));
                        }
                        sdpSqlMapper.insertSelective(paramItem);
                        break;
                    }
                }
            }

            List<String> lines = SplitUtil.splitLines(request.getParameterSql());

            if(!Integer.valueOf(1).equals(request.getIsInterface())) {
                for(String line :lines) {
                    line = line.trim();
                    if(line.startsWith("-- ")) {
                        line = line.substring("-- ".length()).trim();
                        if(line.startsWith("<if") || line.startsWith("<when")) {
                            if(!line.contains("simple.")) {
                                throw new Exception("自定义mybatis <if或<when条件必须使用simple.作为字段前缀");
                            }
                        }
                    }
                }
            }

        }
        int result = super.addSdpSql(request);
        return result;
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public Integer updateSdpSql(SdpSqlUpdateRequest request) throws Exception {
        checkLowercaseName(request.getWorkspaceName());
        checkLowercaseName(request.getTableName());
        checkLowercaseNameWithDot(request.getParameterCatalog());
        checkLowercaseName(request.getParameterCatalogType());
        checkLowercaseName(request.getName());

        if(StringUtils.hasText(request.getParameterCatalogType())) {
            checkCatalogType(request.getParameterCatalogType());
        }
        if (StringUtils.hasText(request.getJavaReturnType())) {
            for(String name: new String[]{"int", "long", "float", "double"}) {
                if (name.equals(request.getJavaReturnType())) {
                    throw new Exception("invalid return type, cannot be base type:"+request.getJavaReturnType());
                }
            }
        }

        SdpSqlWithBLOBs record = sdpSqlMapper.selectByPrimaryKeyWithBLOBs(request.getId());

        if("api.facade".equals(request.getParameterCatalog())) {
            if(StringUtils.hasText(request.getName())) {
                if(!"request".equals(request.getName())) {
                    throw new Exception("api.facade类型的name必须设置request");
                }
            } else {
                if(!"request".equals(record.getName())) {
                    throw new Exception("api.facade类型的name必须设置request");
                }
            }
        }

        if (request.getParameterCatalogType() != null
                || request.getProjectNames() != null
                || request.getTableName() != null
                || request.getWorkspaceName() != null
        ) {
            if ("sql".equals(record.getParameterCatalog()) && request.getParameterCatalog() == null) {
                SdpSqlExample destExample = new SdpSqlExample();
                destExample.createCriteria().andWorkspaceNameEqualTo(record.getWorkspaceName())
                        .andTableNameEqualTo(record.getTableName())
                        .andParameterCatalogNotEqualTo("sql")
                        .andParameterCatalogTypeEqualTo(record.getParameterCatalogType());

                SdpSqlWithBLOBs destBatchUpdateRequest = new SdpSqlWithBLOBs();
                destBatchUpdateRequest.setWorkspaceName(request.getWorkspaceName() != null ? request.getWorkspaceName() : record.getWorkspaceName());
                destBatchUpdateRequest.setTableName(request.getTableName() != null ? request.getTableName() : record.getTableName());
                destBatchUpdateRequest.setProjectNames(request.getProjectNames() != null ? request.getProjectNames() : record.getProjectNames());
                destBatchUpdateRequest.setParameterCatalogType(request.getParameterCatalogType() != null ? request.getParameterCatalogType() : record.getParameterCatalogType());
                sdpSqlMapper.updateByExampleSelective(destBatchUpdateRequest, destExample);

                //fix update time for history
                Thread.sleep(1000);
            }
        }

        record = sdpSqlMapper.selectByPrimaryKeyWithBLOBs(request.getId());

        if(StringUtils.hasText(request.getParameterSql())) {
            {
                String workspaceName = record.getWorkspaceName();

                SdpWorkspaceWithBLOBs workspace = sdpWorkspaceMapper.selectByNameWithBLOBs(workspaceName);

                Connection connection = processSQLFacade.getConnection(workspace);


                PreparedStatement mysqlStatement = null;
                try {
                    String sql = request.getParameterSql();
                    try {
                        mysqlStatement = connection.prepareStatement(sql);
                    } catch (Exception ex) {
                        if((ex.getMessage()+"").contains("This connection has been closed")) {
                            processSQLFacade.closeConnection(workspace);
                        }
                        System.err.println("=== prepareStatement error, sql ===");
                        System.err.println(sql);
                        throw ex;
                    }
                    ResultSetMetaData resultSetMetaData;
                    try {
                        resultSetMetaData = mysqlStatement.getMetaData();
                    } catch (Exception ex) {
                        System.err.println("=== getMetaData error, sql ===");
                        System.err.println(sql);
                        throw new Exception(ex.getMessage());
                    }


                    Object[] jdbcFields = ConnectUtil.getMetaFields(resultSetMetaData);
                    int oriColumnCount = jdbcFields.length;

                    Map<String, Method> methodMap = new HashMap<>();
                    methodMap.put("getName", null);
                    methodMap.put("getTableName", null);
                    methodMap.put("getOriginalTableName", null);
                    methodMap.put("getColumnName", null);
                    methodMap.put("getFullName", null);
                    methodMap.put("getOriginalColumnName", null);
                    methodMap.put("getDatabaseName", null);

                    Map<String, Field> fieldMap = new HashMap<>();
                    fieldMap.put("name", null);
                    fieldMap.put("tableName", null);
                    fieldMap.put("originalTableName", null);
                    fieldMap.put("columnName", null);
                    fieldMap.put("fullName", null);
                    fieldMap.put("originalColumnName", null);
                    fieldMap.put("databaseName", null);
                    fieldMap.put("mysqlType", null);

                    //for postgresql
                    fieldMap.put("columnLabel", null);

                    Class clz = null;
                    if (jdbcFields.length > 0) {
                        clz = jdbcFields[0].getClass();

                        checkSingleStar(sql);

                    }

                } catch (Exception ex) {
                    ex.printStackTrace();
                    throw ex;
                } finally {
                    if (mysqlStatement != null) {
                        mysqlStatement.close();
                    }
                }
//                connection.close();
            }
        }

        return super.updateSdpSql(request);
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public Integer deleteSdpSql(SdpSqlDeleteRequest request) throws Exception {
        SdpSqlExample example = new SdpSqlExample();
        example.createCriteria().andIdIn(request.getIdList());
        List<SdpSqlWithBLOBs> list = sdpSqlMapper.selectByExampleWithBLOBs(example);
        for(SdpSql item: list) {
            SdpSqlWithBLOBs record = sdpSqlMapper.selectByPrimaryKeyWithBLOBs(item.getId());

            if ("sql".equals(item.getParameterCatalog())) {
                SdpSqlExample destExample = new SdpSqlExample();
                destExample.createCriteria().andWorkspaceNameEqualTo(item.getWorkspaceName())
                        .andTableNameEqualTo(item.getTableName())
                        .andParameterCatalogNotEqualTo("sql")
                        .andParameterCatalogTypeEqualTo(item.getParameterCatalogType());
                sdpSqlMapper.deleteByExample(destExample);

                //fix update time for history
                Thread.sleep(1000);
            }
        }

        return super.deleteSdpSql(request);
    }

    @Override
    public List<SdpSqlQueryResponse> listSdpSql(SdpSqlQueryRequest request) throws Exception {
        if(request.getQueryOptions() != null && request.getQueryOptions().containsKey("like")) {
            JSONObject like = request.getQueryOptions().getJSONObject("like");
            if(like.containsKey("table_name")) {
                String name = like.getString("table_name");
                if(name.indexOf("-") > 0) {
                    name = name.split("-")[0];
                    like.put("table_name", name+"%");
                    request.getQueryOptions().put("like", like);
                }
            }
        }
        List<SdpSqlQueryResponse> list = super.listSdpSql(request);
        return list;
    }
    @Override
    public Integer clone(BaseNameIdRequest request) throws Exception {
        checkLowercaseName(request.getName());

        SdpSqlWithBLOBs sdpSqlWithBLOBs = sdpSqlMapper.selectByPrimaryKeyWithBLOBs(request.getId());
        if (request.getName().equals(sdpSqlWithBLOBs.getWorkspaceName())) {
            throw new Exception("dest name is it self");
        }
        SdpSqlExample example = new SdpSqlExample();
        example.createCriteria().andWorkspaceNameEqualTo(sdpSqlWithBLOBs.getWorkspaceName());
        List<SdpSqlWithBLOBs> oriList = sdpSqlMapper.selectByExampleWithBLOBs(example);
        SdpSqlExample destExample = new SdpSqlExample();
        destExample.createCriteria().andWorkspaceNameEqualTo(request.getName());
        List<SdpSqlWithBLOBs> destList = sdpSqlMapper.selectByExampleWithBLOBs(destExample);
        Map<String, SdpSqlWithBLOBs> oriMap = new HashMap<>();
        for(SdpSqlWithBLOBs item: oriList) {
            oriMap.put(item.getTableName()+";"+item.getParameterCatalog()+";"+item.getParameterCatalogType()+";"+item.getName(), item);
        }

        for(SdpSqlWithBLOBs item: destList) {
            SdpSqlWithBLOBs oriItem = oriMap.get(item.getTableName()+";"+item.getParameterCatalog()+";"+item.getParameterCatalogType()+";"+item.getName());
            if (oriItem != null) {
                SdpSqlUpdateRequest destUpdateRequest = new SdpSqlUpdateRequest();
                BeanUtils.copyProperties(oriItem, destUpdateRequest);
                destUpdateRequest.setId(item.getId());
                destUpdateRequest.setWorkspaceName(request.getName());
                updateSdpSql(destUpdateRequest);

                oriMap.remove(item.getTableName()+";"+item.getParameterCatalog()+";"+item.getParameterCatalogType()+";"+item.getName());
            }
        }
        for(SdpSqlWithBLOBs item: oriMap.values()) {
            item.setId(null);
            item.setWorkspaceName(request.getName());
            sdpSqlMapper.insertSelective(item);
        }
        return 1;
    }

}
